package com.util;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import java.util.List;

public class ExcelUtil {
    public static void importExcel(String sheetTitle, String[] heads, List<JSONObject> array, XSSFWorkbook workbook) {


        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(0, sheetTitle);
        for (int i = 0; i < heads.length; i++) {
            //默认列宽
            sheet.setColumnWidth(i, 10 * 256 + 150);
        }

        XSSFCellStyle titleStyle = workbook.createCellStyle();
        Font titleFont = workbook.createFont();
        //设置字体
        titleFont.setFontName("微软雅黑");
        //设置粗体
        titleFont.setBoldweight(Short.MAX_VALUE);
        //设置字号
        titleFont.setFontHeightInPoints((short) 10);
        //设置颜色
        titleFont.setColor(IndexedColors.BLACK.index);
        //水平居中
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFont(titleFont);
        XSSFRow rowTitle = sheet.createRow(0);
        for (int i = 0; i < heads.length; i++) {
            XSSFCell cell = rowTitle.createCell((short) i);

            //设置图案颜色
            titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(182, 184, 192)));
            //设置图案样式
            titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));

            cell.setCellStyle(titleStyle);
            HSSFRichTextString text = new HSSFRichTextString(heads[i]);
            cell.setCellValue(text.toString());
        }
        int i = 1;
        for (JSONObject object : array) {

            XSSFCellStyle titleStyles = workbook.createCellStyle();
            Font titleFonts = workbook.createFont();
            //设置字体
            titleFonts.setFontName("微软雅黑");
            //设置粗体
            titleFonts.setBoldweight(Short.MAX_VALUE);
            //设置字号
            titleFonts.setFontHeightInPoints((short) 10);
            //设置颜色
            titleFonts.setColor(IndexedColors.BLACK.index);
            //水平居中
            titleStyles.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            //垂直居中
            titleStyles.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            titleStyles.setFont(titleFonts);

            XSSFRow row = sheet.createRow(i);
            row.setHeightInPoints((short) 17);
            XSSFCell cellOne = row.createCell((short) 0);
            XSSFCell cellTwo = row.createCell((short) 1);
            XSSFCell cellThree = row.createCell((short) 2);
            XSSFCell cellFour = row.createCell((short) 3);
            XSSFCell cellFive = row.createCell((short) 4);
            XSSFCell cellSix = row.createCell((short) 5);
            XSSFCell cellSeven = row.createCell((short) 6);
            XSSFCell cellEight = row.createCell((short) 7);
            XSSFCell cellNone = row.createCell((short) 8);
            XSSFCell cellTen = row.createCell((short) 9);
            XSSFCell cell11 = row.createCell((short) 10);
            XSSFCell cell12 = row.createCell((short) 11);
            XSSFCell cell13 = row.createCell((short) 12);
            XSSFCell cell14 = row.createCell((short) 13);
            XSSFCell cell15 = row.createCell((short) 14);
            XSSFCell cell16 = row.createCell((short) 15);
            XSSFCell cell17 = row.createCell((short) 16);
            XSSFCell cell18 = row.createCell((short) 17);
            XSSFCell cell19 = row.createCell((short) 18);
            XSSFCell cell20 = row.createCell((short) 19);
            XSSFCell cell21 = row.createCell((short) 20);
            XSSFCell cell22 = row.createCell((short) 21);
            XSSFCell cell23 = row.createCell((short) 22);
            XSSFCell cell24 = row.createCell((short) 23);
            XSSFCell cell25 = row.createCell((short) 24);
            if (object.containsKey("cityCaption")) {
                cellOne.setCellValue(object.getString("cityCaption"));
                cellOne.setCellStyle(titleStyles);
            }
            if (object.containsKey("landCaption")) {
                cellTwo.setCellValue(object.getString("landCaption"));
                cellTwo.setCellStyle(titleStyles);
            }
            if (object.containsKey("landId")) {
                cellThree.setCellValue(object.getString("landId"));
                cellThree.setCellStyle(titleStyles);
            }
            if (object.containsKey("repeatGoal")) {
                cellFour.setCellValue(object.getString("repeatGoal"));
                cellFour.setCellStyle(titleStyles);
            }
            if (object.containsKey("Operator")) {
                cellFive.setCellValue(object.getString("Operator"));
                cellFive.setCellStyle(titleStyles);
            }
            if (object.containsKey("InputTime")) {
                cellSix.setCellValue(object.getString("InputTime"));
                cellSix.setCellStyle(titleStyles);
            }
            if (object.containsKey("CreateDataDateTime")) {
                cellSeven.setCellValue(object.getString("CreateDataDateTime"));
                cellSeven.setCellStyle(titleStyles);
            }
            if (object.containsKey("checkstate")) {
                cellEight.setCellValue(object.getString("checkstate"));
                cellEight.setCellStyle(titleStyles);
            }

            if (object.containsKey("newcheckstate")) {
                cellNone.setCellValue(object.getString("newcheckstate"));
                cellNone.setCellStyle(titleStyles);
            }

            if (object.containsKey("region")) {
                cellTen.setCellValue(object.getString("region"));
                cellTen.setCellStyle(titleStyles);
            }
            if (object.containsKey("plate")) {
                cell11.setCellValue(object.getString("plate"));
                cell11.setCellStyle(titleStyles);
            }
            if (object.containsKey("address")) {
                cell12.setCellValue(object.getString("address"));
                cell12.setCellStyle(titleStyles);
            }
            if (object.containsKey("announcementDate")) {
                cell13.setCellValue(object.getString("announcementDate"));
                cell13.setCellStyle(titleStyles);
            }
            if (object.containsKey("transactionDate")) {
                cell14.setCellValue(object.getString("transactionDate"));
                cell14.setCellStyle(titleStyles);
            }
            if (object.containsKey("transactionSituation")) {
                cell15.setCellValue(object.getString("transactionSituation"));
                cell15.setCellStyle(titleStyles);
            }
            if (object.containsKey("landAttribute")) {
                cell16.setCellValue(object.getString("landAttribute"));
                cell16.setCellStyle(titleStyles);
            }
            if (object.containsKey("announcementNumber")) {
                cell17.setCellValue(object.getString("announcementNumber"));
                cell17.setCellStyle(titleStyles);
            }
            if (object.containsKey("assignee")) {
                cell18.setCellValue(object.getString("assignee"));
                cell18.setCellStyle(titleStyles);
            }
            if (object.containsKey("landState")) {
                cell19.setCellValue(object.getString("landState"));
                cell19.setCellStyle(titleStyles);
            }
            if (object.containsKey("totallandArea")) {
                cell20.setCellValue(object.getString("totallandArea"));
                cell20.setCellStyle(titleStyles);
            }
            if (object.containsKey("transferMethod")) {
                cell21.setCellValue(object.getString("transferMethod"));
                cell21.setCellStyle(titleStyles);
            }
            if (object.containsKey("grossTransactionPrice")) {
                cell22.setCellValue(object.getString("grossTransactionPrice"));
                cell22.setCellStyle(titleStyles);
            }
            if (object.containsKey("landNumber")) {
                cell23.setCellValue(object.getString("landNumber"));
                cell23.setCellStyle(titleStyles);
            }
            if (object.containsKey("source")) {
                cell24.setCellValue(object.getString("source"));
                cell24.setCellStyle(titleStyles);
            }
            if (object.containsKey("scanSource")) {
                cell25.setCellValue(object.getString("scanSource"));
                cell25.setCellStyle(titleStyles);
            }
            i++;
        }
    }

    public static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }
}

